package com.cms.service;

import java.util.ArrayList;
import java.util.List;

import com.cms.model.ImportUserChannelData;
import com.cms.model.ImportUserData;
import com.service.AbstractDateService;
import com.utils.dbcp.ConnectionManager;
import com.utils.dbcp.ModelGenerator;

public class ImportUserDataServiceImpl extends AbstractDateService implements
		ImportUserDataService {

	@Override
	public List<ImportUserData> getImportUserDataByContent(ImportUserData model) {
		String sql = "select * from t_importuserdata where day='"+model.getDay()+"'" +
				" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' and chid='"+model.getChid()+"' " +
				" and operator='"+model.getOperator()+"'";
		ModelGenerator<ImportUserData> dbcpmg = new ModelGenerator<ImportUserData>(ImportUserData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public boolean saveImportUserData(List<ImportUserData> list) {
		String [] sql = new String[list.size()];
		ImportUserData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			List<ImportUserData>  l = getImportUserDataByContent(model);
			if(l!=null && l.size()>0)
			{
				sql[i] = "update t_importuserdata set datafileid='"+model.getDatafileid()+"'," +
						"usersum="+model.getSum()+",fee="+model.getFee()+" where day='"+model.getDay()+"'" +
						" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' and chid='"+model.getChid()+"' " +
						" and operator='"+model.getOperator()+"'";
			}else
			{
				model.setId(getTableId("t_importuserdata"));
				sql[i] = "insert into t_importuserdata (id,datafileid,day,appnumber,appversionnumber,channelid,chid,operator," +
						"usersum,fee) values ('"+model.getId()+"'," +
						"'"+model.getDatafileid()+"','"+model.getDay()+"'" +
						",'"+model.getAppnumber()+"','"+model.getAppversionnumber()+"','"+model.getChannelid()+"','"+model.getChid()+"','"+
						model.getOperator()+"','"+model.getSum()+"',"+model.getFee()+")";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public List<ImportUserData> queryImportUserDataByContent(String startdate,
			String enddate, String appnumber, String appversionnumber,int channelid, String operator,
			String dimension) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appversionnumber");
			gb += "appversionnumber,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			
			sql = "select 0 id,0 datafileid," + gb + "usersum sum,fee fee " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(usersum) usersum,sum(fee) fee from t_importuserdata t1 " +
				"where (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (appnumber != null && !appnumber.equals(""))
				sql += " and appnumber='" + appnumber +"' ";
			
			if (appversionnumber != null && !appversionnumber.equals(""))
				sql += " and appversionnumber='" + appversionnumber +"' ";
			 
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day";
		}
		logger.info("[Sql] [method:queryImportUserDataByContent] ["+sql+"]");
		ModelGenerator<ImportUserData> dbcpmg = new ModelGenerator<ImportUserData>(ImportUserData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public boolean saveImportUserChannelData(List<ImportUserChannelData> list) {
		String [] sql = new String[list.size()];
		ImportUserChannelData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			List<ImportUserChannelData>  l = getImportUserChannelDataByContent(model);
			if(l!=null && l.size()>0)
			{
				sql[i] = "update t_importuserchanneldata set datafileid='"+model.getDatafileid()+"'," +
						"usersum="+model.getSum()+",fee="+model.getFee()+" where day='"+model.getDay()+"'" +
						" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' and chid='"+model.getChid()+"' " +
						" and operator='"+model.getOperator()+"'";
			}else
			{
				model.setId(getTableId("t_importuserchanneldata"));
				sql[i] = "insert into t_importuserchanneldata (id,datafileid,day,appnumber,appversionnumber,channelid,chid,operator," +
						"usersum,fee) values ('"+model.getId()+"'," +
						"'"+model.getDatafileid()+"','"+model.getDay()+"'" +
						",'"+model.getAppnumber()+"','"+model.getAppversionnumber()+"','"+model.getChannelid()+"','"+model.getChid()+"','"+
						model.getOperator()+"','"+model.getSum()+"',"+model.getFee()+")";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public List<ImportUserChannelData> getImportUserChannelDataByContent(
			ImportUserChannelData model) {
		String sql = "select * from t_importuserchanneldata where day='"+model.getDay()+"'" +
				" and appnumber='"+model.getAppnumber()+"' and appversionnumber='"+model.getAppversionnumber()+"' and channelid='"+model.getChannelid()+"' and chid='"+model.getChid()+"' " +
				" and operator='"+model.getOperator()+"'";
		ModelGenerator<ImportUserChannelData> dbcpmg = new ModelGenerator<ImportUserChannelData>(ImportUserChannelData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportUserChannelData> queryImportUserChannelDataByContent(
			String startdate, String enddate, String appnumber,String appversionnumber, int channelid,
			String operator, String dimension) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appversionnumber");
			gb += "appversionnumber,";
			
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			
			sql = "select 0 id,0 datafileid," + gb + "usersum sum,fee fee " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(usersum) usersum,sum(fee) fee from t_importuserchanneldata t1 " +
				"where (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (appnumber != null && !appnumber.equals(""))
				sql += " and appnumber='" + appnumber +"' ";
			
			if (appversionnumber != null && !appversionnumber.equals(""))
				sql += " and appversionnumber='" + appversionnumber +"' ";
			 
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day";
		}
		logger.info("[Sql] [method:queryImportUserDataByContent] ["+sql+"]");
		ModelGenerator<ImportUserChannelData> dbcpmg = new ModelGenerator<ImportUserChannelData>(ImportUserChannelData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportUserData> getImportUserDataUserNumByDay(int startday,int endday) {
		String sql="select day day,appversionnumber appversionnumber,channelid channelid,chid chid,operator operator,sum sum from (select ku.day day,ku.appversionnumber appversionnumber,ku.channelid channelid,ku.chid chid,ku.operatorname operator,sum(ku.newuser) sum from t_kpayusernum ku,t_channel ch where ku.channelid=ch.id and (ch.cooperationtype='cpa' or (ch.id=100024 and ku.appversionnumber='AP31AE99AC9E9BFA3001477E920F058854' )) and ku.day>="+startday+" and ku.day<="+endday+" group by ku.day,ku.appversionnumber,ku.channelid,ku.chid,ku.operatorname) t";
		logger.info("[Sql] [method:getImportUserDataUserNumByDay] ["+sql+"]");
		ModelGenerator<ImportUserData> dbcpmg = new ModelGenerator<ImportUserData>(ImportUserData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportUserData> getImportUserDataFeeByDay(int startday,int endday) {
		String sql="select day day,appversionnumber appversionnumber,channelid channelid,operator operator,fee fee from (select ku.day day,ku.appversionnumber appversionnumber,ku.channelid channelid,ku.operator operator,sum(ku.fee) fee from t_importdata ku,t_channel ch where ku.channelid=ch.id and (ch.cooperationtype='cpa' or (ch.id=100024 and ku.appversionnumber='AP31AE99AC9E9BFA3001477E920F058854' )) and ku.day>="+startday+" and ku.day<="+endday+" group by ku.day,ku.appversionnumber,ku.channelid,ku.operator) t";
		logger.info("[Sql] [method:getImportUserDataFeeByDay] ["+sql+"]");
		ModelGenerator<ImportUserData> dbcpmg = new ModelGenerator<ImportUserData>(ImportUserData.class);
		return dbcpmg.generateList(base, sql);
	}

}
 